<?php
require_once 'common.php';
require_once 'OaAppModel.php';
require_once 'OaCompanyModel.php';
require_once 'OaHscHttpRequest.php';
require_once 'OaHscTcpRequest.php';

/**
 * 统一编码库模型
 * @author wangjiajun
 *
 */
class OaCodeModel extends OpModel
{
    // 是否OA应用
    const APP_FROM_OA = 1;        // OA应用
    const APP_NOT_FROM_OA = 2;    // 非OA应用
    
    // 公司状态
    const COMPANY_STATUS_VALID = 1;      // 有效 
    const COMPANY_STATUS_INVALID = 2;    // 无效
    
    // 应用状态
    const APP_STATUS_VALID = 1;      // 有效 
    const APP_STATUS_INVALID = 2;    // 无效
    
    // 区状态
    const AREA_STATUS_VALID = 1;      // 有效 
    const AREA_STATUS_INVALID = 2;    // 无效
    
    // 组状态
    const GROUP_STATUS_VALID = 1;      // 有效 
    const GROUP_STATUS_INVALID = 2;    // 无效
    
    private $_connCode = null;
    private $_connCodePre = null;
    private $_hscRequestPre;
    private $_hscRequest;
    
    function __construct($logger = null)
    {
        if (is_null($logger)) {
            $logger = getOaModelLogger();
        }
        parent::__construct($logger);
        
        $dbConfig = Oa_Util_Config::getSingleDbConfig(OAII_CODEPRE_DB_CLUSTER_ID);
        $_conn = oci_pconnect($dbConfig['user'], $dbConfig['pass'], 
        	"{$dbConfig['host']}:{$dbConfig['port']}/{$dbConfig['name']}", 'AL32UTF8');
        if (!$_conn) {
            $e = oci_error();
            $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $this->_connCodePre = $_conn;
        
        $dbConfig = Oa_Util_Config::getSingleDbConfig(OAII_CODE_DB_CLUSTER_ID);
        $_conn = oci_pconnect($dbConfig['user'], $dbConfig['pass'], 
        	"{$dbConfig['host']}:{$dbConfig['port']}/{$dbConfig['name']}", 'AL32UTF8');
        if (!$_conn) {
            $e = oci_error();
            $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
            return false;
        }
        $this->_connCode = $_conn;
        
        $url = $GLOBALS['gConfig']['hsc_http_code'];
        $method = HTTP_Request2::METHOD_GET;
        $params = array();
        $this->_hscRequestPre = new OaHscHttpRequest($url, 'M_CODE_PRE', $method, 
            $params, $this->_logger);
        $this->_hscRequest = new OaHscHttpRequest($url, 'M_CODE', $method, $params, 
            $this->_logger);
    }
    
    function __destruct()
    {
        if (!is_null($this->_connCodePre)) {
            oci_close($this->_connCodePre);
        }
        if (!is_null($this->_connCode)) {
            oci_close($this->_connCode);
        }
    }
    
    /**
     * 新建公司
     * @param int $id 公司ID
     * @param string $name 公司名称
     * @param int $status 有效状态，1有效，2无效
     * @param string $notes 备注
     * @param array $row
     * @param int $env
     * @return boolean 如果名称重复，设置错误号1
     */
    public function addCompany($id, $name, $status = 1, $notes = '', $row = array(), 
        $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
    	if (is_array($this->getCompanyByName($name, $env))) {
            $this->_setError(1, "company name already exists");
            return false;
        }
        $isFromOA = isset($row['IS_FROM_OA']) ? $row['IS_FROM_OA'] : 1;
        $updatetime = isset($row['UPDATETIME']) ? $row['UPDATETIME'] : date('Y-m-d H:i:s');
        $sql = "INSERT INTO TB_SP (SP_ID, SP_NAME, STATUS, NOTES, IS_FROM_OA, UPDATETIME) VALUES 
        	('" . escapeRawInputValue($id) . "', '" . escapeRawInputValue($name) 
            . "', '" . escapeRawInputValue($status) . "', '" . escapeRawInputValue($notes) 
            . "', '" . escapeRawInputValue($isFromOA) 
            . "', TO_DATE('" . escapeRawInputValue($updatetime) . "', 'yyyy-mm-dd hh24:mi:ss'))";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M3');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M3');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            return true;
        } else {
            if ($result['RET_KEY'] == '00') {
                return true;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 查询公司
     * @param int $id
     * @param int $env
     * @return array|null|boolean
     */
    public function getCompany($id, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $sql = "SELECT SP_ID,SP_NAME,STATUS,NOTES,IS_FROM_OA,
        	TO_CHAR(UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS UPDATETIME FROM TB_SP 
        	WHERE SP_ID='" . escapeRawInputValue($id) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql);
        } else {
            $result = $this->_hscRequest->sendSql($sql);
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            $row = oci_fetch_assoc($st);
            return $row ? $row : null;
        } else {
            if ($result['RET_KEY'] == '00') {
                return $result['rows'][0];
            } else if ($result['RET_KEY'] == '01') {
                return null;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 根据名称查询公司
     * @param string $name
     * @param int $env
     * @return array|null|boolean
     */
    public function getCompanyByName($name, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $sql = "SELECT SP_ID,SP_NAME,STATUS,NOTES,IS_FROM_OA,
        	TO_CHAR(UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS UPDATETIME FROM TB_SP 
        	WHERE SP_NAME='" . escapeRawInputValue($name) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql);
        } else {
            $result = $this->_hscRequest->sendSql($sql);
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            $row = oci_fetch_assoc($st);
            return $row ? $row : null;
        } else {
            if ($result['RET_KEY'] == '00') {
                return $result['rows'][0];
            } else if ($result['RET_KEY'] == '01') {
                return null;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 查询公司列表
     * @param int $offset
     * @param int $count
     * @param int $env
     * @return array|boolean
     */
    public function getCompanyList($offset, $count, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    { 
        $begin = $offset + 1;
        if (is_null($count)) {
            $count = $this->getCompanyListCount($env);
        }
        if ($count == 0) {
            return array();
        }
        $end = $offset + $count;
    	$sql = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (
        	SELECT SP_ID,SP_NAME,STATUS,NOTES,IS_FROM_OA,
			TO_CHAR(UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS UPDATETIME 
			FROM TB_SP ORDER BY SP_ID) A WHERE ROWNUM <= '" . escapeRawInput($end) . "') 
			WHERE RN >= '" . escapeRawInput($begin) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql);
        } else {
            $result = $this->_hscRequest->sendSql($sql);
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            $companys = array();
            oci_fetch_all($st, $companys, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
            oci_free_statement($st);
            return $companys;
        } else {
            if ($result['RET_KEY'] == '00') {
                return $result['rows'];
            } else if ($result['RET_KEY'] == '01') {
                return array();
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 查询公司列表总数
     * @param int $env
     * @return int|boolean
     */
    public function getCompanyListCount($env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $sql = "SELECT COUNT(*) AS CNT FROM TB_SP";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql);
        } else {
            $result = $this->_hscRequest->sendSql($sql);
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            $row = oci_fetch_row($st);
            return (int) $row[0];
        } else {
            if ($result['RET_KEY'] == '00') {
                return $result['rows'][0]['CNT'];
            } else if ($result['RET_KEY'] == '01') {
                return -1;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 修改公司
     * @param int $id
     * @param array $row
     * @param int $env
     * @return boolean 如果名称重复，设置错误号1
     */
    public function modifyCompany($id, $row, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
    	$company = $this->getCompany($id, $env);
        $row = self::filterUnchangedField($row, $company);
        if (empty($row)) {
            return true;
        }
    	if (isset($row['SP_NAME']) && is_array($this->getCompanyByName($row['SP_NAME'], $env))) {
            $this->_setError(1, "company name already exists");
            return false;
        }        
        $sql = "UPDATE TB_SP SET";
        $sets = array();
        if (isset($row['SP_NAME'])) {
            $sets[] = " SP_NAME='" . escapeRawInput($row['SP_NAME']) . "'";
        }
        if (isset($row['STATUS'])) {
            $sets[] = " STATUS='" . escapeRawInput($row['STATUS']) . "'";
        }
        if (isset($row['NOTES'])) {
            $sets[] = " NOTES='" . escapeRawInput($row['NOTES']) . "'";
        }
        if (isset($row['IS_FROM_OA'])) {
            $sets[] = " IS_FROM_OA='" . escapeRawInput($row['IS_FROM_OA']) . "'";
        }
        if (isset($row['UPDATETIME'])) {
            $sets[] = " UPDATETIME=TO_DATE('" . escapeRawInput($row['UPDATETIME']) . "', 
            	'yyyy-mm-dd hh24:mi:ss')";
        }
        if (empty($sets)) {
        	return true;
        }
        $sql .= implode(',', $sets);
        $sql .= " WHERE SP_ID='" . escapeRawInput($id) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            return true;
        } else {
            if ($result['RET_KEY'] == '00' || $result['RET_KEY'] == '01') {
                return true;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 修改公司ID
     * @param int $id
     * @param int $newId
     * @param int $env
     * @return boolean 如果新ID已存在，设置错误号1
     */
    public function modifyCompanyId($id, $newId, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $id = (int) $id;
        $newId = (int) $newId;
        if ($newId <= 0) {
            $this->_logger->err("new id invalid: new_id-$newId");
            return false;
        }
        if (is_array($this->getCompany($newId, $env))) {
            $this->_setError(1, "new id already exists: new_id-$newId");
            return false;
        }
        $sql = "UPDATE TB_SERVICE SET SP_ID='" . escapeRawInputValue($newId) . 
        	"' WHERE SP_ID='" . escapeRawInputValue($id) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
        } else {
            if ($result['RET_KEY'] != '00' && $result['RET_KEY'] != '01') {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
        $sql = "UPDATE TB_SP SET SP_ID='" . escapeRawInputValue($newId) . 
        	"' WHERE SP_ID='" . escapeRawInputValue($id) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            return true;
        } else {
            if ($result['RET_KEY'] == '00' || $result['RET_KEY'] == '01') {
                return true;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 删除公司
     * @param int $id
     * @param int $env
     * @return boolean
     */
    public function deleteCompany($id, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $sql = "DELETE FROM TB_SP WHERE SP_ID='" . escapeRawInputValue($id) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
        } else {
            if ($result['RET_KEY'] != '00') {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
        $apps = $this->getAppList($id, 0, null, $env);
        foreach ($apps as $app) {
            $this->deleteApp($app['SERVICE_ID'], $env);
        }
        return true;
    }
    
    public static function convertFieldNameOfCompany($row)
    {
        $fieldMap = array(
            'id' => 'SP_ID',
            'name' => 'SP_NAME',
            'status' => 'STATUS',
            'notes' => 'NOTES',
            'is_from_oa' => 'IS_FROM_OA',
            'tm' => 'UPDATETIME'
        );
        $tmp = array();
        foreach ($fieldMap as $key => $value) {
            if (isset($row[$key])) {
                $tmp[$value] = $row[$key];
            }
        }
        return $tmp;
    }
    
    /**
     * 新建应用
     * @param int $id
     * @param string $name
     * @param int $companyId
     * @param int $status
     * @param string $notes
     * @param array $row
     * @param int $env
     * @return boolean 如果应用名称重复，设置错误号1
     */
    public function addApp($id, $name, $companyId, $status = 1, $notes = '', $row = array(),
        $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
    	if (is_array($this->getAppByName($name, $env))) {
            $this->_setError(1, "app name already exists");
            return false;
        }
        $isFromOA = isset($row['IS_FROM_OA']) ? $row['IS_FROM_OA'] : 1;
        $updatetime = isset($row['UPDATETIME']) ? $row['UPDATETIME'] : date('Y-m-d H:i:s');
        $sql = "INSERT INTO TB_SERVICE (SERVICE_ID, SERVICE_NAME, SP_ID, STATUS, 
        	NOTES, IS_FROM_OA, UPDATETIME) VALUES ('" . escapeRawInputValue($id) 
            . "', '" . escapeRawInputValue($name) . "', '" . escapeRawInputValue($companyId) 
            . "', '" . escapeRawInputValue($status) . "', '" . escapeRawInputValue($notes) 
            . "', '" . escapeRawInputValue($isFromOA) 
            . "', TO_DATE('" . escapeRawInputValue($updatetime) . "', 'yyyy-mm-dd hh24:mi:ss'))";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M3');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M3');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            return true;
        } else {
            if ($result['RET_KEY'] == '00') {
                return true;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 查询应用
     * @param int $id
     * @param int $env
     * @return array|null|boolean
     */
    public function getApp($id, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $sql = "SELECT SERVICE_ID,SERVICE_NAME,SP_ID,STATUS,NOTES,IS_FROM_OA,
        	TO_CHAR(UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS UPDATETIME 
        	FROM TB_SERVICE WHERE SERVICE_ID='" . escapeRawInputValue($id) . "'"; 
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql);
        } else {
            $result = $this->_hscRequest->sendSql($sql);
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            $row = oci_fetch_assoc($st);
            return $row ? $row : null;
        } else {
            if ($result['RET_KEY'] == '00') {
                return $result['rows'][0];
            } else if ($result['RET_KEY'] == '01') {
                return null;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 根据名称查询应用
     * @param string $name
     * @param int $env
     * @return array|null|boolean
     */
    public function getAppByName($name, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $sql = "SELECT SERVICE_ID,SERVICE_NAME,SP_ID,STATUS,NOTES,IS_FROM_OA,
        	TO_CHAR(UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS UPDATETIME 
        	FROM TB_SERVICE WHERE SERVICE_NAME='" . escapeRawInputValue($name) . "'"; 
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql);
        } else {
            $result = $this->_hscRequest->sendSql($sql);
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            $row = oci_fetch_assoc($st);
            return $row ? $row : null;
        } else {
            if ($result['RET_KEY'] == '00') {
                return $result['rows'][0];
            } else if ($result['RET_KEY'] == '01') {
                return null;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 查询应用列表
     * @param int $companyId
     * @param int $offset
     * @param int $count
     * @param int $env
     * @return array|boolean
     */
    public function getAppList($companyId, $offset, $count, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $begin = $offset + 1;
        if (is_null($count)) {
            $count = $this->getAppListCount($companyId, $env);
        }
        if ($count == 0) {
            return array();
        }
        $end = $offset + $count;
    	$sql = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (
        	SELECT SERVICE_ID,SERVICE_NAME,SP_ID,STATUS,NOTES,IS_FROM_OA,
        	TO_CHAR(UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS UPDATETIME 
        	FROM TB_SERVICE WHERE SP_ID='" . escapeRawInputValue($companyId) 
    	    . "' ORDER BY SERVICE_ID ) A WHERE ROWNUM <= '" . escapeRawInputValue($end) 
    	    . "') WHERE RN >= '" . escapeRawInputValue($begin) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql);
        } else {
            $result = $this->_hscRequest->sendSql($sql);
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            $apps = array();
            oci_fetch_all($st, $apps, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
            oci_free_statement($st);
            return $apps;
        } else {
            if ($result['RET_KEY'] == '00') {
                return $result['rows'];
            } else if ($result['RET_KEY'] == '01') {
                return array();
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 查询应用列表总数
     * @param int $companyId
     * @param int $env
     * @return int|boolean
     */
    public function getAppListCount($companyId, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $sql = "SELECT COUNT(*) AS CNT FROM TB_SERVICE WHERE SP_ID='" 
        . escapeRawInputValue($companyId) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql);
        } else {
            $result = $this->_hscRequest->sendSql($sql);
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            $row = oci_fetch_row($st);
            return (int) $row[0];
        } else {
            if ($result['RET_KEY'] == '00') {
                return $result['rows'][0]['CNT'];
            } else if ($result['RET_KEY'] == '01') {
                return -1;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 修改应用
     * @param int $id
     * @param array $row
     * @param int $env
     * @return boolean 如果名称重复，设置错误号1
     */
    public function modifyApp($id, $row, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
    	$app = $this->getApp($id, $env);
        $row = self::filterUnchangedField($row, $app);
        if (empty($row)) {
            return true;
        }
    	if (isset($row['SERVICE_NAME']) && is_array($this->getAppByName($row['SERVICE_NAME'], $env))) {
            $this->_setError(1, "app name already exists");
            return false;
        }
        $sql = "UPDATE TB_SERVICE SET";
        $sets = array();
        if (isset($row['SERVICE_NAME'])) {
            $sets[] = " SERVICE_NAME='" . escapeRawInputValue($row['SERVICE_NAME']) . "'";
        }
        if (isset($row['SP_ID'])) {
            $sets[] = " SP_ID='" . escapeRawInputValue($row['SP_ID']) . "'";
        }
        if (isset($row['STATUS'])) {
            $sets[] = " STATUS='" . escapeRawInputValue($row['STATUS']) . "'";
        }
        if (isset($row['NOTES'])) {
            $sets[] = " NOTES='" . escapeRawInputValue($row['NOTES']) . "'";
        }
        if (isset($row['IS_FROM_OA'])) {
            $sets[] = " IS_FROM_OA='" . escapeRawInputValue($row['IS_FROM_OA']) . "'";
        }
        if (isset($row['UPDATETIME'])) {
            $sets[] = " UPDATETIME=TO_DATE('" . escapeRawInputValue($row['UPDATETIME']) 
                . "', 'yyyy-mm-dd hh24:mi:ss')";
        }
        if (empty($sets)) {
        	return true;
        }
        $sql .= implode(',', $sets);
        $sql .= " WHERE SERVICE_ID='" . escapeRawInputValue($id) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            return true;
        } else {
            if ($result['RET_KEY'] == '00' || $result['RET_KEY'] == '01') {
                return true;
            } else {
            	$this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                //$this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 修改应用ID
     * @param int $id
     * @param int $newId
     * @param int $env
     * @return boolean 如果新ID已存在，设置错误号1
     */
    public function modifyAppId($id, $newId, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $id = (int) $id;
        $newId = (int) $newId;
        if ($newId <= 0) {
            $this->_logger->err("new id invalid: new_id-$newId");
            return false;
        }
        if (is_array($this->getApp($newId, $env))) {
            $this->_setError(1, "new id already exists: new_id-$newId");
            return false;
        }
        $sql = "UPDATE TB_AREA SET SERVICE_ID='" . escapeRawInputValue($newId) 
            . "' WHERE SERVICE_ID='" . escapeRawInputValue($id) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
        } else {
            if ($result['RET_KEY'] != '00' && $result['RET_KEY'] != '01') {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
        $sql = "UPDATE TB_GROUP SET SERVICE_ID='" . escapeRawInputValue($newId) 
            . "' WHERE SERVICE_ID='" . escapeRawInputValue($id) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
        } else {
            if ($result['RET_KEY'] != '00' && $result['RET_KEY'] != '01') {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
        $sql = "UPDATE TB_SERVICE SET SERVICE_ID='" . escapeRawInputValue($newId) 
            . "' WHERE SERVICE_ID='" . escapeRawInputValue($id) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            return true;
        } else {
            if ($result['RET_KEY'] == '00' || $result['RET_KEY'] == '01') {
                return true;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 删除应用
     * @param int $id
     * @param int $env
     * @return boolean
     */
    public function deleteApp($id, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $sql = "DELETE FROM TB_SERVICE WHERE SERVICE_ID='" . escapeRawInputValue($id) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
        } else {
            if ($result['RET_KEY'] != '00') {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
        $areas = $this->getAreaList($id, 0, null, $env);
        foreach ($areas as $area) {
            $this->deleteArea($id, $area['AREA_ID'], $env);
        }
        return true;
    }
    
    public static function convertFieldNameOfApp($row)
    {
        $fieldMap = array(
            'id' => 'SERVICE_ID',
            'name' => 'SERVICE_NAME',
            'company_id' => 'SP_ID',
            'status' => 'STATUS',
            'notes' => 'NOTES',
            'is_from_oa' => 'IS_FROM_OA',
            'tm' => 'UPDATETIME'
        );
        $tmp = array();
        foreach ($fieldMap as $key => $value) {
            if (isset($row[$key])) {
                $tmp[$value] = $row[$key];
            }
        }
        return $tmp;
    }
    
    /**
     * 新建分区
     * @param int $appId
     * @param int $areaCode
     * @param string $name
     * @param int $status
     * @param string $notes
     * @param array $row
     * @param int $env
     * @return boolean
     */
    public function addArea($appId, $areaCode, $name, $status = 1, $notes = '', $row = array(),
        $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
    	$regionFlag = isset($row['REGIONFLAG']) ? $row['REGIONFLAG'] : 2;
    	$seeFlag = isset($row['SEEFLAG']) ? $row['SEEFLAG'] : 1;
    	$virtualFlag = isset($row['VIRTUALFLAG']) ? $row['VIRTUALFLAG'] : 2;
    	$beginTime = isset($row['BEGINTIME']) ? $row['BEGINTIME'] : '2000-01-01 00:00:00';
    	$endTime = isset($row['ENDTIME']) ? $row['ENDTIME'] : '2099-01-01 00:00:00';
    	$showBeginTime = isset($row['SHOWBEGINTIME']) ? $row['SHOWBEGINTIME'] : '2000-01-01 00:00:00';
    	$showEndTime = isset($row['SHOWENDTIME']) ? $row['SHOWENDTIME'] : '2099-01-01 00:00:00';
        $updatetime = isset($row['UPDATETIME']) ? $row['UPDATETIME'] : date('Y-m-d H:i:s');
        $sql = "INSERT INTO TB_AREA (SERVICE_ID, AREA_ID, AREA_NAME, STATUS, 
        	NOTES, REGIONFLAG, SEEFLAG, VIRTUALFLAG, BEGINTIME, ENDTIME, SHOWBEGINTIME, 
        	SHOWENDTIME, UPDATETIME) VALUES ('" . escapeRawInputValue($appId) 
            . "', '" . escapeRawInputValue($areaCode) . "', '" . escapeRawInputValue($name) 
            . "', '" . escapeRawInputValue($status) . "', '" . escapeRawInputValue($notes) 
            . "', '" . escapeRawInputValue($regionFlag) . "', '" . escapeRawInputValue($seeFlag) 
            . "', '" . escapeRawInputValue($virtualFlag) . "', 
            TO_DATE('" . escapeRawInputValue($beginTime) . "', 'yyyy-mm-dd hh24:mi:ss'), 
        	TO_DATE('" . escapeRawInputValue($endTime) . "', 'yyyy-mm-dd hh24:mi:ss'), 
        	TO_DATE('" . escapeRawInputValue($showBeginTime) . "', 'yyyy-mm-dd hh24:mi:ss'), 
        	TO_DATE('" . escapeRawInputValue($showEndTime) . "', 'yyyy-mm-dd hh24:mi:ss'), 
        	TO_DATE('" . escapeRawInputValue($updatetime) . "', 'yyyy-mm-dd hh24:mi:ss'))";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M3');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M3');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            return true;
        } else {
            if ($result['RET_KEY'] == '00') {
                return true;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    
    /**
     * 查询分区
     * @param int $appId
     * @param int $areaCode
     * @param int $env
     * @return array|null|boolean
     */
    public function getArea($appId, $areaCode, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
    	$sql = "SELECT SERVICE_ID,AREA_ID,AREA_NAME,STATUS,NOTES,REGIONFLAG,SEEFLAG,VIRTUALFLAG,
    		TO_CHAR(BEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS BEGINTIME,
    		TO_CHAR(ENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS ENDTIME,
    		TO_CHAR(SHOWBEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS SHOWBEGINTIME,
    		TO_CHAR(SHOWENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS SHOWENDTIME,
    		TO_CHAR(UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS UPDATETIME 
    		FROM TB_AREA WHERE SERVICE_ID='" . escapeRawInputValue($appId) 
    	    . "' AND AREA_ID='" . escapeRawInputValue($areaCode) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql);
        } else {
            $result = $this->_hscRequest->sendSql($sql);
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            $row = oci_fetch_assoc($st);
            return $row ? $row : null;
        } else {
            if ($result['RET_KEY'] == '00') {
                return $result['rows'][0];
            } else if ($result['RET_KEY'] == '01') {
                return null;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 查询分区列表
     * @param int $appId
     * @param int $offset
     * @param int $count
     * @param int $env
     * @return array|boolean
     */
    public function getAreaList($appId, $offset, $count, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $begin = $offset + 1;
        if (is_null($count)) {
            $count = $this->getAreaListCount($appId, $env);
        }
        if ($count == 0) {
            return array();
        }
        $end = $offset + $count;
    	$sql = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (
        	SELECT SERVICE_ID,AREA_ID,AREA_NAME,STATUS,NOTES,REGIONFLAG,SEEFLAG,VIRTUALFLAG,
    		TO_CHAR(BEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS BEGINTIME,
    		TO_CHAR(ENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS ENDTIME,
    		TO_CHAR(SHOWBEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS SHOWBEGINTIME,
    		TO_CHAR(SHOWENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS SHOWENDTIME,
    		TO_CHAR(UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS UPDATETIME 
    		FROM TB_AREA WHERE SERVICE_ID='" . escapeRawInputValue($appId) . "' ORDER BY AREA_ID
			) A WHERE ROWNUM <= '" . escapeRawInputValue($end) . "') WHERE RN >= '" 
    	    . escapeRawInputValue($begin) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql);
        } else {
            $result = $this->_hscRequest->sendSql($sql);
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            $areas = array();
            oci_fetch_all($st, $areas, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
            oci_free_statement($st);
            return $areas;
        } else {
            if ($result['RET_KEY'] == '00') {
                return $result['rows'];
            } else if ($result['RET_KEY'] == '01') {
                return array();
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 查询分区列表总数
     * @param int $appId
     * @param int $env
     * @return int|boolean
     */
    public function getAreaListCount($appId, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $sql = "SELECT COUNT(*) AS CNT FROM TB_AREA WHERE SERVICE_ID='" 
            . escapeRawInputValue($appId) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql);
        } else {
            $result = $this->_hscRequest->sendSql($sql);
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            $row = oci_fetch_row($st);
            return (int) $row[0];
        } else {
            if ($result['RET_KEY'] == '00') {
                return $result['rows'][0]['CNT'];
            } else if ($result['RET_KEY'] == '01') {
                return -1;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 修改分区
     * @param int $appId
     * @param int $areaCode
     * @param array $row
     * @param int $env
     * @return boolean
     */
    public function modifyArea($appId, $areaCode, $row, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $sql = "UPDATE TB_AREA SET";
        $sets = array();
        if (isset($row['AREA_NAME'])) {
            $sets[] = " AREA_NAME='" . escapeRawInputValue($row['AREA_NAME']) . "'";
        }
        if (isset($row['STATUS'])) {
            $sets[] = " STATUS='" . escapeRawInputValue($row['STATUS']) . "'";
        }
        if (isset($row['NOTES'])) {
            $sets[] = " NOTES='" . escapeRawInputValue($row['NOTES']) . "'";
        }
        if (isset($row['REGIONFLAG'])) {
            $sets[] = " REGIONFLAG='" . escapeRawInputValue($row['REGIONFLAG']) . "'";
        }
        if (isset($row['SEEFLAG'])) {
            $sets[] = " SEEFLAG='" . escapeRawInputValue($row['SEEFLAG']) . "'";
        }
        if (isset($row['VIRTUALFLAG'])) {
            $sets[] = " VIRTUALFLAG='" . escapeRawInputValue($row['VIRTUALFLAG']) . "'";
        }
        if (isset($row['BEGINTIME'])) {
            $sets[] = " BEGINTIME=TO_DATE('" . escapeRawInputValue($row['BEGINTIME']) . "', 'yyyy-mm-dd hh24:mi:ss')";
        }
        if (isset($row['ENDTIME'])) {
            $sets[] = " ENDTIME=TO_DATE('" . escapeRawInputValue($row['ENDTIME']) . "', 'yyyy-mm-dd hh24:mi:ss')";
        }
        if (isset($row['SHOWBEGINTIME'])) {
            $sets[] = " SHOWBEGINTIME=TO_DATE('" . escapeRawInputValue($row['SHOWBEGINTIME']) . "', 'yyyy-mm-dd hh24:mi:ss')";
        }
        if (isset($row['SHOWENDTIME'])) {
            $sets[] = " SHOWENDTIME=TO_DATE('" . escapeRawInputValue($row['SHOWENDTIME']) . "', 'yyyy-mm-dd hh24:mi:ss')";
        }
        if (isset($row['UPDATETIME'])) {
            $sets[] = " UPDATETIME=TO_DATE('" . escapeRawInputValue($row['UPDATETIME']) . "', 'yyyy-mm-dd hh24:mi:ss')";
        }
        if (empty($sets)) {
        	return true;
        }
        $sql .= implode(',', $sets);
        $sql .= " WHERE SERVICE_ID='" . escapeRawInputValue($appId) 
            . "' AND AREA_ID='" . escapeRawInputValue($areaCode) . "'"; 
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            return true;
        } else {
            if ($result['RET_KEY'] == '00' || $result['RET_KEY'] == '01') {
                return true;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 修改区编码
     * @param int $appId
     * @param int $areaCode
     * @param int $newCode
     * @param int $env
     * @return boolean 如果新编码已存在，设置错误号1
     */
    public function modifyAreaCode($appId, $areaCode, $newCode, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $appId = (int) $appId;
        $areaCode = (int) $areaCode;
        $newCode = (int) $newCode;
        if ($newCode <= 0) {
            $this->_logger->err("new code invalid: new_code-$newCode");
            return false;
        }
        if (is_array($this->getArea($appId, $newCode, $env))) {
            $this->_setError(1, "new code already exists: new_code-$newCode");
            return false;
        }
        $sql = "UPDATE TB_GROUP SET AREA_ID='" . escapeRawInputValue($newCode) 
            . "' WHERE SERVICE_ID='" . escapeRawInputValue($appId) . "' AND AREA_ID='" 
            . escapeRawInputValue($areaCode) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
        } else {
            if ($result['RET_KEY'] != '00' && $result['RET_KEY'] != '01') {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
        $sql = "UPDATE TB_AREA SET AREA_ID='" . escapeRawInputValue($newCode) 
            . "' WHERE SERVICE_ID='" . escapeRawInputValue($appId) . "' AND 
        	AREA_ID='" . escapeRawInputValue($areaCode) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            return true;
        } else {
            if ($result['RET_KEY'] == '00' || $result['RET_KEY'] == '01') {
                return true;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 删除分区
     * @param int $appId
     * @param int $areaCode
     * @param int $env
     * @return boolean
     */
    public function deleteArea($appId, $areaCode, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $sql = "DELETE FROM TB_AREA WHERE SERVICE_ID='" . escapeRawInputValue($appId) 
            . "' AND AREA_ID='" . escapeRawInputValue($areaCode) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
        } else {
            if ($result['RET_KEY'] != '00') {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
        $groups = $this->getGroupList($appId, $areaCode, 0, null, $env);
        foreach ($groups as $group) {
            $this->deleteGroup($appId, $areaCode, $group['GROUP_ID'], $env);
        }
        return true;
    }
    
    public static function convertFieldNameOfArea($row)
    {
        $fieldMap = array(
            'area_code' => 'AREA_ID',
            'status' => 'STATUS',
            'name' => 'AREA_NAME',
            'app_id' => 'SERVICE_ID',
            'notes' => 'NOTES',
            'region_flag' => 'REGIONFLAG',
            'see_flag' => 'SEEFLAG',
            'virtual_flag' => 'VIRTUALFLAG',
            'begin_time' => 'BEGINTIME',
            'end_time' => 'ENDTIME',
            'show_begin_time' => 'SHOWBEGINTIME',
            'show_end_time' => 'SHOWENDTIME',
            'tm' => 'UPDATETIME'
        );
        $tmp = array();
        foreach ($fieldMap as $key => $value) {
            if (isset($row[$key])) {
                $tmp[$value] = $row[$key];
            }
        }
        return $tmp;
    }
    
    /**
     * 新建分组
     * @param int $appId
     * @param int $areaCode
     * @param int $groupCode
     * @param string $name
     * @param int $status
     * @param string $notes
     * @param array $row
     * @param int $env
     * @return boolean
     */
    public function addGroup($appId, $areaCode, $groupCode, $name, $status = 1, 
        $notes = '', $row = array(), $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {    	
    	$seeFlag = isset($row['SEEFLAG']) ? $row['SEEFLAG'] : 1;
    	$virtualFlag = isset($row['VIRTUALFLAG']) ? $row['VIRTUALFLAG'] : 2;
    	$beginTime = isset($row['BEGINTIME']) ? $row['BEGINTIME'] : date('Y-m-d H:i:s');
    	$endTime = isset($row['ENDTIME']) ? $row['ENDTIME'] : '2099-01-01 00:00:00';
    	$showBeginTime = isset($row['SHOWBEGINTIME']) ? $row['SHOWBEGINTIME'] : date('Y-m-d H:i:s');
    	$showEndTime = isset($row['SHOWENDTIME']) ? $row['SHOWENDTIME'] : '2099-01-01 00:00:00';
        $updatetime = isset($row['UPDATETIME']) ? $row['UPDATETIME'] : date('Y-m-d H:i:s');
        $sql = "INSERT INTO TB_GROUP (SERVICE_ID, AREA_ID, GROUP_ID, GROUP_NAME, STATUS, 
        	NOTES, SEEFLAG, VIRTUALFLAG, BEGINTIME, ENDTIME, SHOWBEGINTIME, SHOWENDTIME, UPDATETIME) 
        	VALUES ('" . escapeRawInputValue($appId) . "', '" . escapeRawInputValue($areaCode) 
            . "', '" . escapeRawInputValue($groupCode) . "', '" . escapeRawInputValue($name) 
            . "', '" . escapeRawInputValue($status) . "', '" . escapeRawInputValue($notes) 
            . "', '" . escapeRawInputValue($seeFlag) . "', '" . escapeRawInputValue($virtualFlag) . "', 
        	TO_DATE('" . escapeRawInputValue($beginTime) . "', 'yyyy-mm-dd hh24:mi:ss'), 
        	TO_DATE('" . escapeRawInputValue($endTime) . "', 'yyyy-mm-dd hh24:mi:ss'), 
        	TO_DATE('" . escapeRawInputValue($showBeginTime) . "', 'yyyy-mm-dd hh24:mi:ss'), 
        	TO_DATE('" . escapeRawInputValue($showEndTime) . "', 'yyyy-mm-dd hh24:mi:ss'), 
        	TO_DATE('" . escapeRawInputValue($updatetime) . "', 'yyyy-mm-dd hh24:mi:ss'))";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M3');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M3');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            return true;
        } else {
            if ($result['RET_KEY'] == '00') {
                return true;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    
    /**
     * 查询分组
     * @param int $appId
     * @param int $areaCode
     * @param int $groupCode
     * @param int $env
     * @return array|null|boolean
     */
    public function getGroup($appId, $areaCode, $groupCode, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
    	$sql = "SELECT SERVICE_ID,AREA_ID,GROUP_ID,GROUP_NAME,STATUS,NOTES,SEEFLAG,VIRTUALFLAG,
    		TO_CHAR(BEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS BEGINTIME,
    		TO_CHAR(ENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS ENDTIME,
    		TO_CHAR(SHOWBEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS SHOWBEGINTIME,
    		TO_CHAR(SHOWENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS SHOWENDTIME,
    		TO_CHAR(UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS UPDATETIME 
    		FROM TB_GROUP WHERE SERVICE_ID='" . escapeRawInputValue($appId) 
    	    . "' AND AREA_ID='" . escapeRawInputValue($areaCode) . "' AND GROUP_ID='" 
    	    . escapeRawInputValue($groupCode) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql);
        } else {
            $result = $this->_hscRequest->sendSql($sql);
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            $row = oci_fetch_assoc($st);
            return $row ? $row : null;
        } else {
            if ($result['RET_KEY'] == '00') {
                return $result['rows'][0];
            } else if ($result['RET_KEY'] == '01') {
                return null;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 查询分组列表
     * @param int $appId
     * @param int $areaCode
     * @param int $offset
     * @param int $count
     * @param int $env
     * @return array|boolean
     */
    public function getGroupList($appId, $areaCode, $offset, $count, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $begin = $offset + 1;
        if (is_null($count)) {
            $count = $this->getGroupListCount($appId, $areaCode, $env);
        }
        if ($count == 0) {
            return array();
        }
        $end = $offset + $count;
    	$sql = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (
    		SELECT SERVICE_ID,AREA_ID,GROUP_ID,GROUP_NAME,STATUS,NOTES,SEEFLAG,VIRTUALFLAG,
    		TO_CHAR(BEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS BEGINTIME,
    		TO_CHAR(ENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS ENDTIME,
    		TO_CHAR(SHOWBEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS SHOWBEGINTIME,
    		TO_CHAR(SHOWENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS SHOWENDTIME,
    		TO_CHAR(UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS UPDATETIME 
    		FROM TB_GROUP WHERE SERVICE_ID='" . escapeRawInputValue($appId) . "' AND AREA_ID='" 
    	    . escapeRawInputValue($areaCode) . "' ORDER BY GROUP_ID) A WHERE ROWNUM <= '" 
    	    . escapeRawInputValue($end) . "') WHERE RN >= '" 
    	    . escapeRawInputValue($begin) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql);
        } else {
            $result = $this->_hscRequest->sendSql($sql);
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            $groups = array();
            oci_fetch_all($st, $groups, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
            oci_free_statement($st);
            return $groups;
        } else {
            if ($result['RET_KEY'] == '00') {
                return $result['rows'];
            } else if ($result['RET_KEY'] == '01') {
                return array();
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 查询分组列表总数
     * @param int $appId
     * @param int $areaCode
     * @param int $env
     * @return int|boolean
     */
    public function getGroupListCount($appId, $areaCode, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $sql = "SELECT COUNT(*) AS CNT FROM TB_GROUP WHERE SERVICE_ID='" 
            . escapeRawInputValue($appId) . "' AND AREA_ID='" 
            . escapeRawInputValue($areaCode) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql);
        } else {
            $result = $this->_hscRequest->sendSql($sql);
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            $row = oci_fetch_row($st);
            return (int) $row[0];
        } else {
            if ($result['RET_KEY'] == '00') {
                return $result['rows'][0]['CNT'];
            } else if ($result['RET_KEY'] == '01') {
                return -1;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 修改分组
     * @param int $appId
     * @param int $areaCode
     * @param int $groupCode
     * @param array $row
     * @param int $env
     * @return boolean
     */
    public function modifyGroup($appId, $areaCode, $groupCode, $row, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $sql = "UPDATE TB_GROUP SET";
        $sets = array();
        if (isset($row['GROUP_NAME'])) {
            $sets[] = " GROUP_NAME='" . escapeRawInputValue($row['GROUP_NAME']) . "'";
        }
        if (isset($row['STATUS'])) {
            $sets[] = " STATUS='" . escapeRawInputValue($row['STATUS']) . "'";
        }
        if (isset($row['NOTES'])) {
            $sets[] = " NOTES='" . escapeRawInputValue($row['NOTES']) . "'";
        }
        if (isset($row['SEEFLAG'])) {
            $sets[] = " SEEFLAG='" . escapeRawInputValue($row['SEEFLAG']) . "'";
        }
        if (isset($row['VIRTUALFLAG'])) {
            $sets[] = " VIRTUALFLAG='" . escapeRawInputValue($row['VIRTUALFLAG']) . "'";
        }
        if (isset($row['BEGINTIME'])) {
            $sets[] = " BEGINTIME=TO_DATE('" . escapeRawInputValue($row['BEGINTIME']) . "', 'yyyy-mm-dd hh24:mi:ss')";
        }
        if (isset($row['ENDTIME'])) {
            $sets[] = " ENDTIME=TO_DATE('" . escapeRawInputValue($row['ENDTIME']) . "', 'yyyy-mm-dd hh24:mi:ss')";
        }
        if (isset($row['SHOWBEGINTIME'])) {
            $sets[] = " SHOWBEGINTIME=TO_DATE('" . escapeRawInputValue($row['SHOWBEGINTIME']) . "', 'yyyy-mm-dd hh24:mi:ss')";
        }
        if (isset($row['SHOWENDTIME'])) {
            $sets[] = " SHOWENDTIME=TO_DATE('" . escapeRawInputValue($row['SHOWENDTIME']) . "', 'yyyy-mm-dd hh24:mi:ss')";
        }
        if (isset($row['UPDATETIME'])) {
            $sets[] = " UPDATETIME=TO_DATE('" . escapeRawInputValue($row['UPDATETIME']) . "', 'yyyy-mm-dd hh24:mi:ss')";
        }
        if (empty($sets)) {
        	return true;
        }
        $sql .= implode(',', $sets);
        $sql .= " WHERE SERVICE_ID='" . escapeRawInputValue($appId) 
            . "' AND AREA_ID='" . escapeRawInputValue($areaCode) 
            . "' AND GROUP_ID='" . escapeRawInputValue($groupCode) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            return true;
        } else {
            if ($result['RET_KEY'] == '00' || $result['RET_KEY'] == '01') {
                return true;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 修改组编码
     * @param int $appId
     * @param int $areaCode
     * @param int $groupCode
     * @param int $newCode
     * @param int $env
     * @return boolean 如果新编码已存在，设置错误号1
     */
    public function modifyGroupCode($appId, $areaCode, $groupCode, $newCode, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $appId = (int) $appId;
        $areaCode = (int) $areaCode;
        $groupCode = (int) $groupCode;
        $newCode = (int) $newCode;
        if ($newCode <= 0) {
            $this->_logger->err("new code invalid: new_code-$newCode");
            return false;
        }
        if (is_array($this->getGroup($appId, $areaCode, $newCode, $env))) {
            $this->_setError(1, "new code already exists: new_code-$newCode");
            return false;
        }
        $sql = "UPDATE TB_GROUP SET GROUP_ID='" . escapeRawInput($newCode) 
            . "' WHERE SERVICE_ID='" . escapeRawInput($appId) . "' AND AREA_ID='" 
            . escapeRawInput($areaCode) . "' AND GROUP_ID='" . escapeRawInput($groupCode) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            return true;
        } else {
            if ($result['RET_KEY'] == '00' || $result['RET_KEY'] == '01') {
                return true;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 删除分组
     * @param int $appId
     * @param int $areaCode
     * @param int $groupCode
     * @param int $env
     * @return boolean
     */
    public function deleteGroup($appId, $areaCode, $groupCode, $env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        $sql = "DELETE FROM TB_GROUP WHERE SERVICE_ID='" . escapeRawInput($appId) 
            . "' AND AREA_ID='" . escapeRawInput($areaCode) . "' AND GROUP_ID='" 
            . escapeRawInput($groupCode) . "'";
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) { 
            $result = $this->_hscRequestPre->sendSql($sql, 'M2');
        } else {
            $result = $this->_hscRequest->sendSql($sql, 'M2');
        }
        if ($result === false) {
            $this->_logger->warning("hsc request failed");
            if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
                $st = oci_parse($this->_connCodePre, $sql);
            } else {
                $st = oci_parse($this->_connCode, $sql);
            }
            if (!oci_execute($st)) {
                $e = oci_error($st);
                $this->_logger->err("[{$e['code']}] {$e['message']}: {$e['sqltext']}");
                return false;
            }
            return true;
        } else {
            if ($result['RET_KEY'] == '00') {
                return true;
            } else {
                $this->_logger->err(__FUNCTION__."\t{$sql}\treturn unsuccess: {$result['RET_KEY']}");
                return false;
            }
        }
    }
    
    /**
     * 获取底层数据库连接
     * @param int $env
     * @return resource|boolean
     */
    public function getCodeDbConn($env = OaAppModel::APP_CONFIG_ENV_TEST)
    {
        if ($env == OaAppModel::APP_CONFIG_ENV_TEST) {
            return $this->_connCodePre;
        } else if ($env == OaAppModel::APP_CONFIG_ENV_PRODUCTION) {
            return $this->_connCode;
        } else {
            return false;
        }
    }
    
    public static function convertFieldNameOfGroup($row)
    {
        $fieldMap = array(
            'group_code' => 'GROUP_ID',
            'status' => 'STATUS',
            'name' => 'GROUP_NAME',
            'area_code' => 'AREA_ID',
            'app_id' => 'SERVICE_ID',
            'notes' => 'NOTES',
            'see_flag' => 'SEEFLAG',
            'virtual_flag' => 'VIRTUALFLAG',
            'begin_time' => 'BEGINTIME',
            'end_time' => 'ENDTIME',
            'show_begin_time' => 'SHOWBEGINTIME',
            'show_end_time' => 'SHOWENDTIME',
            'tm' => 'UPDATETIME'
        );
        $tmp = array();
        foreach ($fieldMap as $key => $value) {
            if (isset($row[$key])) {
                $tmp[$value] = $row[$key];
            }
        }
        return $tmp;
    }
    
    /**
     * 过滤掉没有改变的字段
     * @param array $row
     * @param array $oldRow
     * @return array
     */
    public static function filterUnchangedField($row, $oldRow)
    {
    	foreach ($row as $key => $value) {
    		if (isset($oldRow[$key]) && $row[$key] == $oldRow[$key]) {
    			unset($row[$key]);
    		}
    	}
    	return $row;
    }
    
    public function unicode($operation, $env, $cust, $spId = -1, $serviceId = -1, 
    	$areaId = -1, $beginTime = '', $endTime = '')
    {
    	$sps = array();
    	$rows = array();
    	$codeDbConn = $this->getCodeDbConn($env);
        if ($operation == 1) {
	        $sql = "SELECT t.GROUP_ID,t.GROUP_NAME,t.STATUS AS GROUP_STATUS,
	        	t.NOTES AS GROUP_NOTES,t.SEEFLAG AS GROUP_SEEFLAG,t.VIRTUALFLAG AS GROUP_VIRTUALFLAG,
	    		TO_CHAR(t.BEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS GROUP_BEGINTIME,
	    		TO_CHAR(t.ENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS GROUP_ENDTIME,
	    		TO_CHAR(t.SHOWBEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS GROUP_SHOWBEGINTIME,
	    		TO_CHAR(t.SHOWENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS GROUP_SHOWENDTIME,
	    		TO_CHAR(t.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS GROUP_UPDATETIME, 
	    		t1.AREA_ID,t1.AREA_NAME,t1.STATUS AS AREA_STATUS,t1.NOTES AS AREA_NOTES,
	    		t1.REGIONFLAG AS AREA_REGIONFLAG,t1.SEEFLAG AS AREA_SEEFLAG,
	    		t1.VIRTUALFLAG AS AREA_VIRTUALFLAG,
	    		TO_CHAR(t1.BEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS AREA_BEGINTIME,
	    		TO_CHAR(t1.ENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS AREA_ENDTIME,
	    		TO_CHAR(t1.SHOWBEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS AREA_SHOWBEGINTIME,
	    		TO_CHAR(t1.SHOWENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS AREA_SHOWENDTIME,
	    		TO_CHAR(t1.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS AREA_UPDATETIME, 
	    		t2.SERVICE_ID,t2.SERVICE_NAME,t2.STATUS AS SERVICE_STATUS,t2.NOTES AS SERVICE_NOTES,
        		TO_CHAR(t2.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS SERVICE_UPDATETIME,
        		t3.SP_ID,t3.SP_NAME,t3.STATUS AS SP_STATUS,t3.NOTES AS SP_NOTES,
				TO_CHAR(t3.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS SP_UPDATETIME
	    		FROM TB_SP t3 
	    		LEFT JOIN TB_SERVICE t2 ON (t2.SP_ID=t3.SP_ID) 
	    		LEFT JOIN TB_AREA t1 ON (t1.SERVICE_ID=t2.SERVICE_ID) 
	    		LEFT JOIN TB_GROUP t ON (t.SERVICE_ID=t1.SERVICE_ID AND t.AREA_ID=t1.AREA_ID)";
        	$wheres = array();
	        if ($spId >= 0) {
	        	$wheres[] = "t3.SP_ID=:spId";
	        }
	        if (!empty($wheres)) {
	        	$sql .= " WHERE " . implode(' AND ', $wheres);
	        }
        	$sql .= " ORDER BY t3.SP_ID,t2.SERVICE_ID,t1.AREA_ID,t.GROUP_ID";
	        $st = oci_parse($codeDbConn, $sql);
	        if ($spId >= 0) {
            	oci_bind_by_name($st, ':spId', $spId);
	        }
	        if (!oci_execute($st)) {
	            $e = oci_error($st);
	            $this->_setError(-1, "db error: code-{$e['code']}, message-{$e['message']}, sql-{$e['sqltext']}");
	            return false;
	        }
	        oci_fetch_all($st, $rows, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
	        oci_free_statement($st);
	        $this->_parseRows($rows, $sps);
        } else if ($operation == 2 || $operation == 3) {
	        $sql = "SELECT t.GROUP_ID,t.GROUP_NAME,t.STATUS AS GROUP_STATUS,
	        	t.NOTES AS GROUP_NOTES,t.SEEFLAG AS GROUP_SEEFLAG,t.VIRTUALFLAG AS GROUP_VIRTUALFLAG,
	    		TO_CHAR(t.BEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS GROUP_BEGINTIME,
	    		TO_CHAR(t.ENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS GROUP_ENDTIME,
	    		TO_CHAR(t.SHOWBEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS GROUP_SHOWBEGINTIME,
	    		TO_CHAR(t.SHOWENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS GROUP_SHOWENDTIME,
	    		TO_CHAR(t.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS GROUP_UPDATETIME, 
	    		t1.AREA_ID,t1.AREA_NAME,t1.STATUS AS AREA_STATUS,t1.NOTES AS AREA_NOTES,
	    		t1.REGIONFLAG AS AREA_REGIONFLAG,t1.SEEFLAG AS AREA_SEEFLAG,
	    		t1.VIRTUALFLAG AS AREA_VIRTUALFLAG,
	    		TO_CHAR(t1.BEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS AREA_BEGINTIME,
	    		TO_CHAR(t1.ENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS AREA_ENDTIME,
	    		TO_CHAR(t1.SHOWBEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS AREA_SHOWBEGINTIME,
	    		TO_CHAR(t1.SHOWENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS AREA_SHOWENDTIME,
	    		TO_CHAR(t1.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS AREA_UPDATETIME, 
	    		t2.SERVICE_ID,t2.SERVICE_NAME,t2.STATUS AS SERVICE_STATUS,t2.NOTES AS SERVICE_NOTES,
        		TO_CHAR(t2.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS SERVICE_UPDATETIME,
        		t3.SP_ID,t3.SP_NAME,t3.STATUS AS SP_STATUS,t3.NOTES AS SP_NOTES,
				TO_CHAR(t3.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS SP_UPDATETIME
	    		FROM TB_GROUP t 
	    		LEFT JOIN TB_AREA t1 ON (t.SERVICE_ID=t1.SERVICE_ID AND t.AREA_ID=t1.AREA_ID) 
	    		LEFT JOIN TB_SERVICE t2 ON (t1.SERVICE_ID=t2.SERVICE_ID) 
	    		LEFT JOIN TB_SP t3 ON (t2.SP_ID=t3.SP_ID)";
        	$wheres = array();
	        if ($spId >= 0) {
	        	$wheres[] = "t3.SP_ID=:spId";
	        }
	        if ($operation == 3) {
	            if ($serviceId >= 0) {
		        	$wheres[] = "t2.SERVICE_ID=:serviceId";
	            }
	            if ($areaId >= 0) {
		        	$wheres[] = "t1.AREA_ID=:areaId";
	            }
	        }
	        if (!empty($beginTime)) {
	        	$wheres[] = "t.UPDATETIME>=TO_DATE(:beginTime, 'YYYY-MM-DD HH24:MI:SS')";
	        }
	        if (!empty($endTime)) {
	        	$wheres[] = "t.UPDATETIME<TO_DATE(:endTime, 'YYYY-MM-DD HH24:MI:SS')";
	        }
	        if (!empty($wheres)) {
	        	$sql .= " WHERE " . implode(' AND ', $wheres);
	        }
        	$sql .= " ORDER BY t3.SP_ID,t2.SERVICE_ID,t1.AREA_ID,t.GROUP_ID";
	        $st = oci_parse($codeDbConn, $sql);
	        if ($spId >= 0) {
            	oci_bind_by_name($st, ':spId', $spId);
	        }
	        if ($operation == 3) {
	            if ($serviceId >= 0) {
            		oci_bind_by_name($st, ':serviceId', $serviceId);
	            }
	            if ($areaId >= 0) {
            		oci_bind_by_name($st, ':areaId', $areaId);
	            }
	        }
	        if (!empty($beginTime)) {
            	oci_bind_by_name($st, ':beginTime', $beginTime);
	        }
	        if (!empty($endTime)) {
            	oci_bind_by_name($st, ':endTime', $endTime);
	        }
	        if (!oci_execute($st)) {
	            $e = oci_error($st);
	            $this->_setError(-1, "db error: code-{$e['code']}, message-{$e['message']}, sql-{$e['sqltext']}");
	            return false;
	        }
	        oci_fetch_all($st, $rows, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
	        oci_free_statement($st);
	        $this->_parseRows($rows, $sps);
	        
	        $sql = "SELECT t1.AREA_ID,t1.AREA_NAME,t1.STATUS AS AREA_STATUS,t1.NOTES AS AREA_NOTES,
	    		t1.REGIONFLAG AS AREA_REGIONFLAG,t1.SEEFLAG AS AREA_SEEFLAG,
	    		t1.VIRTUALFLAG AS AREA_VIRTUALFLAG,
	    		TO_CHAR(t1.BEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS AREA_BEGINTIME,
	    		TO_CHAR(t1.ENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS AREA_ENDTIME,
	    		TO_CHAR(t1.SHOWBEGINTIME, 'YYYY-MM-DD HH24:MI:SS') AS AREA_SHOWBEGINTIME,
	    		TO_CHAR(t1.SHOWENDTIME, 'YYYY-MM-DD HH24:MI:SS') AS AREA_SHOWENDTIME,
	    		TO_CHAR(t1.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS AREA_UPDATETIME, 
	    		t2.SERVICE_ID,t2.SERVICE_NAME,t2.STATUS AS SERVICE_STATUS,t2.NOTES AS SERVICE_NOTES,
        		TO_CHAR(t2.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS SERVICE_UPDATETIME,
        		t3.SP_ID,t3.SP_NAME,t3.STATUS AS SP_STATUS,t3.NOTES AS SP_NOTES,
				TO_CHAR(t3.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS SP_UPDATETIME
	    		FROM TB_AREA t1 
	    		LEFT JOIN TB_SERVICE t2 ON (t1.SERVICE_ID=t2.SERVICE_ID) 
	    		LEFT JOIN TB_SP t3 ON (t2.SP_ID=t3.SP_ID)";
        	$wheres = array();
	        if ($spId >= 0) {
	        	$wheres[] = "t3.SP_ID=:spId";
	        }
	        if ($operation == 3) {
	            if ($serviceId >= 0) {
		        	$wheres[] = "t2.SERVICE_ID=:serviceId";
	            }
	            if ($areaId >= 0) {
		        	$wheres[] = "t1.AREA_ID=:areaId";
	            }
	        }
	        if (!empty($beginTime)) {
	        	$wheres[] = "t1.UPDATETIME>=TO_DATE(:beginTime, 'YYYY-MM-DD HH24:MI:SS')";
	        }
	        if (!empty($endTime)) {
	        	$wheres[] = "t1.UPDATETIME<TO_DATE(:endTime, 'YYYY-MM-DD HH24:MI:SS')";
	        }
	        if (!empty($wheres)) {
	        	$sql .= " WHERE " . implode(' AND ', $wheres);
	        }
        	$sql .= " ORDER BY t3.SP_ID,t2.SERVICE_ID,t1.AREA_ID";
	        $st = oci_parse($codeDbConn, $sql);
	        if ($spId >= 0) {
            	oci_bind_by_name($st, ':spId', $spId);
	        }
	        if ($operation == 3) {
	            if ($serviceId >= 0) {
            		oci_bind_by_name($st, ':serviceId', $serviceId);
	            }
	            if ($areaId >= 0) {
            		oci_bind_by_name($st, ':areaId', $areaId);
	            }
	        }
	        if (!empty($beginTime)) {
            	oci_bind_by_name($st, ':beginTime', $beginTime);
	        }
	        if (!empty($endTime)) {
            	oci_bind_by_name($st, ':endTime', $endTime);
	        }
	        if (!oci_execute($st)) {
	            $e = oci_error($st);
	            $this->_setError(-1, "db error: code-{$e['code']}, message-{$e['message']}, sql-{$e['sqltext']}");
	            return false;
	        }
	        oci_fetch_all($st, $rows, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
	        oci_free_statement($st);
	        $this->_parseRows($rows, $sps);
	        
	        $sql = "SELECT t2.SERVICE_ID,t2.SERVICE_NAME,t2.STATUS AS SERVICE_STATUS,t2.NOTES AS SERVICE_NOTES,
        		TO_CHAR(t2.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS SERVICE_UPDATETIME,
        		t3.SP_ID,t3.SP_NAME,t3.STATUS AS SP_STATUS,t3.NOTES AS SP_NOTES,
				TO_CHAR(t3.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS SP_UPDATETIME
	    		FROM TB_SERVICE t2 
	    		LEFT JOIN TB_SP t3 ON (t2.SP_ID=t3.SP_ID)";
        	$wheres = array();
	        if ($spId >= 0) {
	        	$wheres[] = "t3.SP_ID=:spId";
	        }
	        if ($operation == 3) {
	            if ($serviceId >= 0) {
		        	$wheres[] = "t2.SERVICE_ID=:serviceId";
	            }
	        }
	        if (!empty($beginTime)) {
	        	$wheres[] = "t2.UPDATETIME>=TO_DATE(:beginTime, 'YYYY-MM-DD HH24:MI:SS')";
	        }
	        if (!empty($endTime)) {
	        	$wheres[] = "t2.UPDATETIME<TO_DATE(:endTime, 'YYYY-MM-DD HH24:MI:SS')";
	        }
	        if (!empty($wheres)) {
	        	$sql .= " WHERE " . implode(' AND ', $wheres);
	        }
        	$sql .= " ORDER BY t3.SP_ID,t2.SERVICE_ID";
	        $st = oci_parse($codeDbConn, $sql);
	        if ($spId >= 0) {
            	oci_bind_by_name($st, ':spId', $spId);
	        }
	        if ($operation == 3) {
	            if ($serviceId >= 0) {
            		oci_bind_by_name($st, ':serviceId', $serviceId);
	            }
	        }
	        if (!empty($beginTime)) {
            	oci_bind_by_name($st, ':beginTime', $beginTime);
	        }
	        if (!empty($endTime)) {
            	oci_bind_by_name($st, ':endTime', $endTime);
	        }
	        if (!oci_execute($st)) {
	            $e = oci_error($st);
	            $this->_setError(-1, "db error: code-{$e['code']}, message-{$e['message']}, sql-{$e['sqltext']}");
	            return false;
	        }
	        oci_fetch_all($st, $rows, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
	        oci_free_statement($st);
	        $this->_parseRows($rows, $sps);
	        
	        $sql = "SELECT t3.SP_ID,t3.SP_NAME,t3.STATUS AS SP_STATUS,t3.NOTES AS SP_NOTES,
				TO_CHAR(t3.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS SP_UPDATETIME
	    		FROM TB_SP t3";
        	$wheres = array();
	        if ($spId >= 0) {
	        	$wheres[] = "t3.SP_ID=:spId";
	        }
	        if (!empty($beginTime)) {
	        	$wheres[] = "t3.UPDATETIME>=TO_DATE(:beginTime, 'YYYY-MM-DD HH24:MI:SS')";
	        }
	        if (!empty($endTime)) {
	        	$wheres[] = "t3.UPDATETIME<TO_DATE(:endTime, 'YYYY-MM-DD HH24:MI:SS')";
	        }
	        if (!empty($wheres)) {
	        	$sql .= " WHERE " . implode(' AND ', $wheres);
	        }
        	$sql .= " ORDER BY t3.SP_ID";
	        $st = oci_parse($codeDbConn, $sql);
	        if ($spId >= 0) {
            	oci_bind_by_name($st, ':spId', $spId);
	        }
	        if (!empty($beginTime)) {
            	oci_bind_by_name($st, ':beginTime', $beginTime);
	        }
	        if (!empty($endTime)) {
            	oci_bind_by_name($st, ':endTime', $endTime);
	        }
	        if (!oci_execute($st)) {
	            $e = oci_error($st);
	            $this->_setError(-1, "db error: code-{$e['code']}, message-{$e['message']}, sql-{$e['sqltext']}");
	            return false;
	        }
	        oci_fetch_all($st, $rows, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
	        oci_free_statement($st);
	        $this->_parseRows($rows, $sps);
	        
	        ksort($sps);
	        foreach ($sps as &$sp) {
	        	ksort($sp['services']);
	        	foreach ($sp['services'] as &$service) {
	        		ksort($service['areas']);
	        	}
	        }
	        unset($sp);
	        unset($service);
        } else if ($operation == 4) {
	        $sql = "SELECT t.SERVICE_ID,t.SERVICE_NAME,t.STATUS AS SERVICE_STATUS,
	        	t.NOTES AS SERVICE_NOTES,
        		TO_CHAR(t.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS SERVICE_UPDATETIME, 
        		t1.SP_ID,t1.SP_NAME,t1.STATUS AS SP_STATUS,t1.NOTES AS SP_NOTES,
				TO_CHAR(t1.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS SP_UPDATETIME
	    		FROM TB_SP t1 
	    		LEFT JOIN TB_SERVICE t ON (t.SP_ID=t1.SP_ID)";
        	$wheres = array();
	        if ($spId >= 0) {
	        	$wheres[] = "t1.SP_ID=:spId";
	        }
	        if (!empty($wheres)) {
	        	$sql .= " WHERE " . implode(' AND ', $wheres);
	        }
        	$sql .= " ORDER BY t1.SP_ID,t.SERVICE_ID";
	        $st = oci_parse($codeDbConn, $sql);
	        if ($spId >= 0) {
            	oci_bind_by_name($st, ':spId', $spId);
	        }
	        if (!oci_execute($st)) {
	            $e = oci_error($st);
	            $this->_setError(-1, "db error: code-{$e['code']}, message-{$e['message']}, sql-{$e['sqltext']}");
	            return false;
	        }
	        oci_fetch_all($st, $rows, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
	        oci_free_statement($st);
	        $this->_parseRows($rows, $sps);
	        
	        $sql = "SELECT t1.SP_ID,t1.SP_NAME,t1.STATUS AS SP_STATUS,t1.NOTES AS SP_NOTES,
				TO_CHAR(t1.UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') AS SP_UPDATETIME
	    		FROM TB_SP t1";
        	$wheres = array();
        	$wheres[] = "t1.STATUS=1";
	        if ($spId >= 0) {
	        	$wheres[] = "t1.SP_ID=:spId";
	        }
	        if (!empty($wheres)) {
	        	$sql .= " WHERE " . implode(' AND ', $wheres);
	        }
        	$sql .= " ORDER BY t1.SP_ID";
	        $st = oci_parse($codeDbConn, $sql);
	        if ($spId >= 0) {
            	oci_bind_by_name($st, ':spId', $spId);
	        }
	        if (!oci_execute($st)) {
	            $e = oci_error($st);
	            $this->_setError(-1, "db error: code-{$e['code']}, message-{$e['message']}, sql-{$e['sqltext']}");
	            return false;
	        }
	        oci_fetch_all($st, $rows, null, null, OCI_FETCHSTATEMENT_BY_ROW + OCI_ASSOC);
	        oci_free_statement($st);
	        $this->_parseRows($rows, $sps);
	        
	        ksort($sps);
        }
        
        $doc = new DOMDocument('1.0', 'GBK');
        $doc->formatOutput = true;
        $unicode = $doc->appendChild($doc->createElement('unicode'));
        foreach ($sps as $sp) {
            if (($operation == 1 || $operation == 4) && $sp['STATUS'] != 1) {
                continue;
            }
        	if ($cust) {
        		$sp['STATUS'] -= 1;
        		$sp['UPDATETIME'] = substr($sp['UPDATETIME'], 0, 10);
        	}
            $spElement = $unicode->appendChild($this->_spDOMElement($doc, $sp));
            foreach ($sp['services'] as $service) {
                if (($operation == 1 || $operation == 4) && $service['STATUS'] != 1) {
                    continue;
                }
	        	if ($cust) {
	        		$service['STATUS'] -= 1;
	        		$service['UPDATETIME'] = substr($service['UPDATETIME'], 0, 10);
	        	}
                $serviceElement = $spElement->appendChild($this->_serviceDOMElement($doc, $service));
                foreach ($service['areas'] as $area) {
                    if (($operation == 1 || $operation == 4) && $area['STATUS'] != 1) {
                        continue;
                    }
		        	if ($cust) {
		        		$area['STATUS'] -= 1;
		        		$area['UPDATETIME'] = substr($area['UPDATETIME'], 0, 10);
		        		$area['SHOWBEGINTIME'] = substr($area['SHOWBEGINTIME'], 0, 10);
		        		$area['SHOWENDTIME'] = substr($area['SHOWENDTIME'], 0, 10);
		        		$area['BEGINTIME'] = substr($area['BEGINTIME'], 0, 10);
		        		$area['ENDTIME'] = substr($area['ENDTIME'], 0, 10);
		        	}
                    $areaElement = $serviceElement->appendChild($this->_areaDOMElement($doc, $area));
                    foreach ($area['groups'] as $group) {
                        if (($operation == 1 || $operation == 4) && $group['STATUS'] != 1) {
                            continue;
                        }
			        	if ($cust) {
			        		$group['STATUS'] -= 1;
			        		$group['UPDATETIME'] = substr($group['UPDATETIME'], 0, 10);
			        		$group['SHOWBEGINTIME'] = substr($group['SHOWBEGINTIME'], 0, 10);
			        		$group['SHOWENDTIME'] = substr($group['SHOWENDTIME'], 0, 10);
			        		$group['BEGINTIME'] = substr($group['BEGINTIME'], 0, 10);
			        		$group['ENDTIME'] = substr($group['ENDTIME'], 0, 10);
			        	}
                        $areaElement->appendChild($this->_groupDOMElement($doc, $group));
                    }
                }
            }
        }
        return $doc->saveXML();
    }
    
    private function _parseRows($rows, &$sps)
    {
    	if (!is_array($sps)) {
    		$sps = array();
    	}
        foreach ($rows as $row) {
        	if (isset($row['SP_ID']) && !is_null($row['SP_ID'])) {
        		$spId = (int) $row['SP_ID'];
        	} else {
        		continue;
        	}
        	if (!isset($sps[$spId])) {
	        	$sps[$spId] = array(
	        		'SP_ID' => $row['SP_ID'],
	        		'SP_NAME' => $row['SP_NAME'],
	        		'STATUS' => $row['SP_STATUS'],
	        		'UPDATETIME' => $row['SP_UPDATETIME'],
	        		'NOTES' => $row['SP_NOTES'],
	        		'services' => array()
	        	);
        	}
        	$sp = &$sps[$spId];
        	if (isset($row['SERVICE_ID']) && !is_null($row['SERVICE_ID'])) {
        		$serviceId = (int) $row['SERVICE_ID'];
        	} else {
        		continue;
        	}
        	if (!isset($sp['services'][$serviceId])) {
        		$sp['services'][$serviceId] = array(
        			'SERVICE_ID' => $row['SERVICE_ID'],
        			'SERVICE_NAME' => $row['SERVICE_NAME'],
        			'STATUS' => $row['SERVICE_STATUS'],
        			'NOTES' => $row['SERVICE_NOTES'],
        			'UPDATETIME' => $row['SERVICE_UPDATETIME'],
        			'areas' => array()
        		);
        	}
        	$service = &$sp['services'][$serviceId];
        	if (isset($row['AREA_ID']) && !is_null($row['AREA_ID'])) {
        		$areaId = (int) $row['AREA_ID'];
        	} else {
        		continue;
        	}
        	if (!isset($service['areas'][$areaId])) {
        		$service['areas'][$areaId] = array(
        			'AREA_ID' => $row['AREA_ID'],
        			'AREA_NAME' => $row['AREA_NAME'],
        			'STATUS' => $row['AREA_STATUS'],
        			'NOTES' => $row['AREA_NOTES'],
        			'SEEFLAG' => $row['AREA_SEEFLAG'],
        			'SHOWBEGINTIME' => $row['AREA_SHOWBEGINTIME'],
        			'SHOWENDTIME' => $row['AREA_SHOWENDTIME'],
        			'VIRTUALFLAG' => $row['AREA_VIRTUALFLAG'],
        			'REGIONFLAG' => $row['AREA_REGIONFLAG'],
        			'BEGINTIME' => $row['AREA_BEGINTIME'],
        			'ENDTIME' => $row['AREA_ENDTIME'],
        			'UPDATETIME' => $row['AREA_UPDATETIME'],
        			'groups' => array()
        		);
        	}
        	$area = &$service['areas'][$areaId];
        	if (isset($row['GROUP_ID']) && !is_null($row['GROUP_ID'])) {
        		$groupId = (int) $row['GROUP_ID'];
        	} else {
        		continue;
        	}
        	if (!isset($area['groups'][$groupId])) {
        		$area['groups'][$groupId] = array(
        			'GROUP_ID' => $row['GROUP_ID'],
        			'GROUP_NAME' => $row['GROUP_NAME'],
        			'NOTES' => $row['GROUP_NOTES'],
        			'SEEFLAG' => $row['GROUP_SEEFLAG'],
        			'SHOWBEGINTIME' => $row['GROUP_SHOWBEGINTIME'],
        			'SHOWENDTIME' => $row['GROUP_SHOWENDTIME'],
        			'VIRTUALFLAG' => $row['GROUP_VIRTUALFLAG'],
        			'STATUS' => $row['GROUP_STATUS'],
        			'BEGINTIME' => $row['GROUP_BEGINTIME'],
        			'ENDTIME' => $row['GROUP_ENDTIME'],
        			'UPDATETIME' => $row['GROUP_UPDATETIME']
        		);
        	}
        }
    }
    
    private function _spDOMElement($doc, $sp)
    {
        $element = $doc->createElement('sp');
        $element->setAttribute('id', $sp['SP_ID']);
        $element->setAttribute('name', $sp['SP_NAME']);
        $element->setAttribute('status', $sp['STATUS']);
        $element->setAttribute('updatetime', $sp['UPDATETIME']);
        $element->setAttribute('notes', $sp['NOTES']);
        return $element;
    }
    
    private function _serviceDOMElement($doc, $service)
    {
        $element = $doc->createElement('service');
        $element->setAttribute('id', $service['SERVICE_ID']);
        $element->setAttribute('name', $service['SERVICE_NAME']);
        $element->setAttribute('status', $service['STATUS']);
        $element->setAttribute('updatetime', $service['UPDATETIME']);
        $element->setAttribute('notes', $service['NOTES']);
        return $element;
    }
    
    private function _areaDOMElement($doc, $area)
    {
        $element = $doc->createElement('area');
        $element->setAttribute('id', $area['AREA_ID']);
        $element->setAttribute('name', $area['AREA_NAME']);
        $element->setAttribute('isPublic', $area['SEEFLAG']);
        $element->setAttribute('publicBegintime', $area['SHOWBEGINTIME']);
        $element->setAttribute('publicEndtime', $area['SHOWENDTIME']);
        $element->setAttribute('isVirtual', $area['VIRTUALFLAG']);
        $element->setAttribute('regionFlag', $area['REGIONFLAG']);
        $element->setAttribute('status', $area['STATUS']);
        $element->setAttribute('begintime', $area['BEGINTIME']);
        $element->setAttribute('endtime', $area['ENDTIME']);
        $element->setAttribute('updatetime', $area['UPDATETIME']);
        $element->setAttribute('notes', $area['NOTES']);
        return $element;
    }
    
    private function _groupDOMElement($doc, $group)
    {
        $element = $doc->createElement('group');
        $element->setAttribute('id', $group['GROUP_ID']);
        $element->setAttribute('name', $group['GROUP_NAME']);
        $element->setAttribute('isPublic', $group['SEEFLAG']);
        $element->setAttribute('publicBegintime', $group['SHOWBEGINTIME']);
        $element->setAttribute('publicEndtime', $group['SHOWENDTIME']);
        $element->setAttribute('isVirtual', $group['VIRTUALFLAG']);
        $element->setAttribute('status', $group['STATUS']);
        $element->setAttribute('begintime', $group['BEGINTIME']);
        $element->setAttribute('endtime', $group['ENDTIME']);
        $element->setAttribute('updatetime', $group['UPDATETIME']);
        $element->setAttribute('notes', $group['NOTES']);
        return $element;
    }
}